我們知道,像InnoDB、MyISAM這樣的儲存引擎都是把資料存在磁碟裡,而作業系統是使用檔案系統管理磁碟的,所以我們也可以說像InnoDB、MyISAM這樣的儲存引擎都是把資料存在檔案系統上。
當我們要讀取時,儲存引擎從檔案系統中讀取資料出來給我們,寫入的時候,儲存引擎一樣將資料寫回檔案系統中。
今天就是要說明InnoDB、MyISAM這兩個儲存引擎的資料如何在檔案系統中儲存。
先來看看Mysql的資料目錄在那裡
mysql> show variables like 'datadir';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| datadir | /usr/local/var/mysql/ |
+---------------+-----------------------+
1 row in set (0.23 sec)
以下為我本機的Mysql資料庫
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| my_database |
| mydatabase |
| mysql |
| performance_schema |
| ryan_demo_db |
| sys |
+--------------------+
7 rows in set (0.07 sec)
再來Mysql運行過程中會產生許多資料,像是我們建立的資料庫、表以及一些讓程式順利運行額外的資料等。
我們一個個來看
當建立資料庫的時候Mysql會做兩件事:
看看我的Mysql資料目錄
(base) ➜ bin cd /usr/local/var/mysql
(base) ➜ mysql ls -alt
total 12608648
-rwxrwxrwx 1 ryanlai admin 50331648 9 10 18:27 ib_logfile0
-rwxrwxrwx 1 ryanlai admin 27262976 9 10 18:27 undo_002
-rwxrwxrwx 1 ryanlai admin 27262976 9 10 18:27 undo_001
-rw-r----- 1 ryanlai admin 196608 9 10 18:27 #ib_16384_0.dblwr
-rwxrwxrwx 1 ryanlai admin 12582912 9 10 18:27 ibdata1
-rwxrwxrwx 1 ryanlai admin 50331648 9 10 18:27 ib_logfile1
-rw-r----- 1 ryanlai admin 844245421 9 10 18:27 binlog.000020
-rwxrwxrwx 1 ryanlai admin 41943040 9 10 18:15 mysql.ibd
drwxrwxrwx 51 ryanlai admin 1632 9 10 18:00 .
-rw-r----- 1 ryanlai admin 256 9 10 18:00 binlog.index
-rw-r----- 1 ryanlai admin 1073742006 9 10 18:00 binlog.000019
-rw-r----- 1 ryanlai admin 1073742006 9 10 17:07 binlog.000018
-rw-r----- 1 ryanlai admin 1073742163 9 10 16:12 binlog.000017
-rw-r----- 1 ryanlai admin 1073741930 9 10 15:05 binlog.000016
-rw-r----- 1 ryanlai admin 1073742113 9 10 14:26 binlog.000015
-rw-r----- 1 ryanlai admin 8585216 9 10 14:12 #ib_16384_1.dblwr
drwxr-x--- 6 ryanlai admin 192 9 10 13:42 ryan_demo_db
-rw-r----- 1 ryanlai admin 12582912 9 10 10:10 ibtmp1
-rw-r----- 1 ryanlai admin 4441 9 10 10:09 MacBook-Pro-76.local.err
-rw-r----- 1 ryanlai admin 6 9 10 10:09 MacBook-Pro-76.local.pid
-rw-r----- 1 ryanlai admin 156 9 10 10:09 binlog.000014
drwxr-x--- 12 ryanlai admin 384 9 10 10:09 #innodb_temp
-rw-r----- 1 ryanlai admin 3664 9 9 12:43 binlog.000013
drwxr-x--- 2 ryanlai admin 64 9 5 15:36 mydatabase
drwxr-x--- 5 ryanlai admin 160 9 3 16:33 my_database
-rw-r----- 1 ryanlai admin 6 9 3 14:08 MacBook-Pro-76.pid
-rw-r----- 1 ryanlai admin 156 9 3 14:08 binlog.000012
-rw-r----- 1 ryanlai admin 156 9 3 13:41 binlog.000011
-rw-r----- 1 ryanlai admin 156 9 3 11:16 binlog.000010
-rw-r----- 1 ryanlai admin 156 9 3 11:08 binlog.000009
-rw-r----- 1 ryanlai admin 156 9 3 10:58 binlog.000008
-rw-r----- 1 ryanlai admin 200 9 3 10:54 binlog.000007
-rw-r----- 1 ryanlai admin 8709 9 3 10:39 ib_buffer_pool
-rw-r----- 1 ryanlai admin 179 9 3 10:39 binlog.000006
-rw-r----- 1 ryanlai admin 6 9 2 12:49 mysql_upgrade_info
drwxrwxrwx 8 ryanlai admin 256 9 2 12:49 mysql
-rw-r----- 1 ryanlai admin 5905 9 2 12:48 MacBook-Pro-76.err
-rw-r----- 1 ryanlai admin 346 9 2 12:48 binlog.000005
drwxrwxrwx 112 ryanlai admin 3584 7 10 12:57 performance_schema
drwxrwxr-x 11 ryanlai admin 352 6 16 11:53 ..
drwxrwxrwx 3 ryanlai admin 96 3 31 2019 sys
-rw-r----- 1 _mysql admin 592 7 17 2018 Ryande-Air.err
-rw-r----- 1 ryanlai admin 56 7 17 2018 auto.cnf
-rwxrwxrwx 1 ryanlai admin 452 7 17 2018 public_key.pem
-rwxrwxrwx 1 ryanlai admin 1680 7 17 2018 private_key.pem
-rwxrwxrwx 1 ryanlai admin 1112 7 17 2018 client-cert.pem
-rwxrwxrwx 1 ryanlai admin 1676 7 17 2018 client-key.pem
-rwxrwxrwx 1 ryanlai admin 1112 7 17 2018 server-cert.pem
-rwxrwxrwx 1 ryanlai admin 1676 7 17 2018 server-key.pem
-rwxrwxrwx 1 ryanlai admin 1112 7 17 2018 ca.pem
-rwxrwxrwx 1 ryanlai admin 1680 7 17 2018 ca-key.pem
可以看到除了information_schema這個系統資料庫外(MySQL的工程師特殊處理),其他資料庫在資料目錄下都有對應的子目錄。
再看到ryan_demo_db資料庫裏面
(base) ➜ mysql cd ryan_demo_db
(base) ➜ ryan_demo_db ls -alt
total 1794592
-rw-r----- 1 ryanlai admin 901775360 9 10 18:27 per2.ibd
drwxrwxrwx 51 ryanlai admin 1632 9 10 18:00 ..
-rw-r----- 1 ryanlai admin 11534336 9 10 13:56 single_table.ibd
drwxr-x--- 6 ryanlai admin 192 9 10 13:42 .
-rw-r----- 1 ryanlai admin 114688 9 7 16:29 ryan_demo2_table.ibd
-rw-r----- 1 ryanlai admin 114688 9 7 15:40 ryan_demo_table.ibd
卻沒有看到子目錄裡面並沒有db.opt的檔案(因為我是Mysql8.0版本)。
我們的資料其實都是以紀錄的形式插入到表中。
表的資訊有兩個部分:
表結構定義。表的名稱是什麼、表有多少列、每列的資料類型是什麼、有什麼限制條件跟索引、用什麼字元集和比較規則等資訊。
表中的資料
這些描述表結構的資訊會在對應的資料庫子目錄下(ex:/usr/local/var/mysql/ryan_demo_db)創建.frm檔來保存(但一樣在Mysql8.0移除了)。
而表中的資料在不同的儲存引擎會有不同的方式。
Innodb
設計Innodb的工程師提出了表格空間(檔案空間)的概念。這是一個抽象概念,它可以對應檔案系統上一個或多個真實檔案(不同表格空間對應的檔案數量可能不同)。每個表格空間被劃分為很多頁,表資料就存放在表格空間下的頁裡面。
表格空間有幾種類型
(base) ➜ mysql ls -alt
total 12608648
-rwxrwxrwx 1 ryanlai admin 50331648 9 10 18:27 ib_logfile0
-rwxrwxrwx 1 ryanlai admin 27262976 9 10 18:27 undo_002
-rwxrwxrwx 1 ryanlai admin 27262976 9 10 18:27 undo_001
-rw-r----- 1 ryanlai admin 196608 9 10 18:27 #ib_16384_0.dblwr
-rwxrwxrwx 1 ryanlai admin 12582912 9 10 18:27 ibdata1
(base) ➜ ryan_demo_db ls -alt
total 1794592
-rw-r----- 1 ryanlai admin 901775360 9 10 18:27 per2.ibd
drwxrwxrwx 51 ryanlai admin 1632 9 10 18:00 ..
-rw-r----- 1 ryanlai admin 11534336 9 10 13:56 single_table.ibd
drwxr-x--- 6 ryanlai admin 192 9 10 13:42 .
-rw-r----- 1 ryanlai admin 114688 9 7 16:29 ryan_demo2_table.ibd
-rw-r----- 1 ryanlai admin 114688 9 7 15:40 ryan_demo_table.ibd
這裡每一個.ibd檔案就是對應的獨立表格空間在檔案系統裡的表示(一個table一個.ibd檔)
補充說明,可以由啟動選項innodb_file_per_table決定是否要將表資料儲存在系統表格空間(innodb_file_per_table=0)還是獨立表格空間(innodb_file_per_table=1)。
MyISAM
主要差異是,Innodb資料和索引是同一回事,但MyISAM是分開的,在檔案系統會是用不同的檔案來存放資料和索引。MyISAM也沒有表格空間一說,資料和索引就存放在對應的資料庫目錄下。
我的MyISAM資料庫(Mysql8.0)如下
(base) ➜ mysql cd my_database
(base) ➜ my_database ls -alt
total 16
drwxrwxrwx 51 ryanlai admin 1632 9 13 13:22 ..
-rw-r----- 1 ryanlai admin 1024 9 3 16:33 new_table.MYI
drwxr-x--- 5 ryanlai admin 160 9 3 16:33 .
-rw-r----- 1 ryanlai admin 0 9 3 16:33 new_table.MYD
-rw-r----- 1 ryanlai admin 2786 9 3 16:33 new_table_892.sdi
.MYD就是表的資料檔案(使用者紀錄),.MYI表示表的索引檔案,.sdi就是表的結構(Mysql8.0版本以前是.frm檔)。
因為Mysql資料都是存在檔案系統的,因此就不得不受到檔案系統的一些限制。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| my_database |
| mydatabase |
| mysql |
| performance_schema |
| ryan_demo_db |
| sys |
+--------------------+
7 rows in set (0.07 sec)